package com.gzw.scm.DEL;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.gzw.scm.ConfigureLog4J;
import com.gzw.scm.bean.BDInfo;
import com.gzw.scm.bean.PDInfo;
import com.gzw.scm.bean.SCPDInfo;
import com.gzw.scm.utils.L;

import org.apache.log4j.Logger;

import java.util.ArrayList;
import java.util.List;


/**
 * Created by Administrator on 2017/4/25.
 */

public class DBManager {
    private DBHelper helper;
    private SQLiteDatabase db;
    private Context mContext;
    private Logger log;

    public DBManager(Context context) {
        mContext = context;
        helper = new DBHelper(context);
        //因为getWritableDatabase内部调用了mContext.openOrCreateDatabase(mName, 0, mFactory);
        //所以要确保context已初始化,我们可以把实例化DBManager的步骤放在Activity的onCreate里
        db = helper.getWritableDatabase();

        //加载配置
        ConfigureLog4J configureLog4J=new ConfigureLog4J();
        configureLog4J.configure();
        log=Logger.getLogger(this.getClass());

    }

    /**
     * query all persons, return cursor
     *
     * @return Cursor
     */
    private Cursor queryTheCursor(String sql) {
        Cursor c = db.rawQuery(sql, null);
        return c;
    }

    /**
     * close database
     */
    public void closeDB() {
        db.close();
    }

    /**
    *判断分配的周转箱编号是否已存在
     * */
    public Boolean IsExistZzxbh(String bh,String type,Integer did){
        String sql = "select * from tzzxinfo where lx="+type+" and bh="+bh+"and did="+did;
        Cursor c = null;
        Boolean isExist = false;
        try {
            c = db.rawQuery(" select * from tzzxinfo WHERE lx=? and bh=? and did=?", new String[]{type,bh,String.valueOf(did)});

            if (c.getCount() > 0) {
                isExist = true;
            }
        } catch (Exception e) {
            isExist=false;
            log.info("IsExistZzxbh:"+e.toString()+"/bh="+bh+"/type="+type+"/did="+did);
            L.e("IsExistZzxbh", e.toString());
        } finally {
            c.close();
        }
        return isExist;
    }

    /**
     *保存分配的周转箱编号
     * */
    public void addZzxbh(String bh,String type,Integer did) {
        db.beginTransaction();  //开始事务
        try {
            db.execSQL("INSERT INTO tzzxinfo(id,bh,lx,did) VALUES(null, " +
                    "?, ?, ?)", new Object[]{bh,type,String.valueOf(did)});
            db.setTransactionSuccessful();  //设置事务成功完成
        } catch (Exception e) {
            log.info("addZzxbh:"+e.toString()+"/bh="+bh+"/type="+type+"/did="+did);
            L.e("addZzxbh", e.toString());
        } finally {
            db.endTransaction();    //结束事务
        }
    }

    /**
     *查询分配的周转箱编号
     * */
    public List<String> selectFPZzxbh(String type,Integer did){
        Cursor c = null;
        List<String> ls_bh = new ArrayList<>();
        try {
            c = db.rawQuery(" select * from tzzxinfo WHERE lx=? and did=? order by id desc", new String[]{type,String.valueOf(did)});
            while (c.moveToNext()) {
                ls_bh.add(c.getString(c.getColumnIndex("bh")));
            }
        } catch (Exception e) {
            log.info("selectFPZzxbh:"+e.toString()+"/type="+type+"/did="+did);
            L.e("selectFPZzxbh", e.toString());
        } finally {
            c.close();
        }
        return ls_bh;
    }

    /**
     *删除周转箱编号
     * */
    public void deleteFPZzxbh(List<String> ls_bh,String type,Integer did){
        db.beginTransaction();  //开始事务
        try {
            for(String id:ls_bh) {
            db.execSQL("delete from tzzxinfo WHERE lx= ? and bh= ? and did= ?", new Object[]{type,id,String.valueOf(did)});
            }
            db.setTransactionSuccessful();  //设置事务成功完成
        } catch (Exception e) {
            log.info("deleteFPZzxbh:"+e.toString()+"/ls_bh="+ls_bh.toString()+"/type="
                    +type+"/did="+did);
            L.e("deleteFPZzxbh", e.toString());
        } finally {
            db.endTransaction();    //结束事务
        }
    }

    /**
     *保存绑定信息
     * */
    public void addBdinfo(BDInfo bdInfo, String datatype, Integer did, String zt) {
        db.beginTransaction();  //开始事务
        try {
            db.execSQL("INSERT INTO tbdinfo(id,zzxbh,xm,lx,zt,did) VALUES(null, ?, ?, ?, ? ,?)", new Object[]{bdInfo.getZzxbh(),bdInfo.getXm(),datatype,zt,String.valueOf(did)});
            db.setTransactionSuccessful();  //设置事务成功完成
        } catch (Exception e) {
            log.info("addBdinfo:"+e.toString()+"/bdInfo="+bdInfo.toString()+"/zt="+zt+"/did="+did);
            L.e("addBdinfo", e.toString());
        } finally {
            db.endTransaction();    //结束事务
        }
    }

    /**
     * 判断是否已绑定一维码或二维码
     * */
    public Boolean isBdCode1(String code,Integer did,String dataType){

        Cursor c = null;
        Boolean isExist = false;
        try {
            c = db.rawQuery(" select * from tbdinfo WHERE xm=? and did=? and lx=?", new String[]{code,String.valueOf(did),dataType});

            if (c.getCount() > 0) {
                isExist = true;
            }
        } catch (Exception e) {
            isExist=false;
            log.info("isBdCode:"+e.toString()+"/code="+code+"/did="+did+"/dataType="+dataType);
            L.e("isBdCode", e.toString());
        } finally {
            c.close();
        }
        return isExist;
    }

    public Boolean isBdCode2(String code,Integer did,String dataType,String zt){

        Cursor c = null;
        Boolean isExist = false;
        try {
            c = db.rawQuery(" select * from tbdinfo WHERE zzxbh=? and did=?  and lx=?  and zt=?", new String[]{code,String.valueOf(did),dataType,zt});
            if (c.getCount() > 0) {
                isExist = true;
            }
        } catch (Exception e) {
            isExist=false;
            log.info("isBdCode:"+e.toString()+"/code="+code+"/did="+did+"/dataType="
                    +dataType+"/zt="+zt);
            L.e("isBdCode", e.toString());
        } finally {
            c.close();
        }
        return isExist;
    }


    /**
     * 绑定
    * 查询绑定信息
    * */
    public List<BDInfo> SelectBDInfo(Integer did, String dataType){
        Cursor c = null;
        List<BDInfo> ls_bdinfo = new ArrayList<>();
        try {
            c = db.rawQuery(" select * from tbdinfo WHERE did=? and lx=? order by id desc", new String[]{String.valueOf(did),dataType});
            while (c.moveToNext()) {
                BDInfo bdInfo = new BDInfo();
                bdInfo.setZzxbh(c.getString(c.getColumnIndex("zzxbh")));
                bdInfo.setXm(c.getString(c.getColumnIndex("xm")));
                bdInfo.setZt(c.getString(c.getColumnIndex("zt")));
                ls_bdinfo.add(bdInfo);
            }
        } catch (Exception e) {
            log.info("SelectBDInfo:"+e.toString()+"/did="+did+"/dataType="+dataType);
            L.e("SelectBDInfo", e.toString());
        } finally {
            c.close();
        }
        return ls_bdinfo;
    }

    public BDInfo SelectBDInfoByzzx(String zzx,Integer did,String dataType){
        Cursor c = null;
        List<BDInfo> ls_bdinfo = new ArrayList<>();
        BDInfo bdInfo = new BDInfo();
        try {
            c = db.rawQuery(" select * from tbdinfo WHERE zzxbh=? and did=? and lx=?", new String[]{zzx,String.valueOf(did),dataType});
            while (c.moveToNext()) {
                bdInfo.setZzxbh(c.getString(c.getColumnIndex("zzxbh")));
                bdInfo.setXm(c.getString(c.getColumnIndex("xm")));
                bdInfo.setZt(c.getString(c.getColumnIndex("zt")));
            }
        } catch (Exception e) {
            log.info("SelectBDInfoByzzx:"+e.toString()+"/zzx="+zzx+"/did="+did+"/datatype="+dataType);
            L.e("SelectBDInfoByzzx", e.toString());
        } finally {
            c.close();
        }
        return bdInfo;
    }

    /**
     *删除绑定信息
     * */
    public void deleteBDInfo(List<BDInfo> ls_bh,Integer did,String dataType){

        db.beginTransaction();  //开始事务
        try {
            for(BDInfo bdInfo:ls_bh) {
                if(bdInfo.getXm()==null||"".equals(bdInfo.getXm())){
                 db.execSQL("delete from tbdinfo WHERE  zzxbh= ? and did= ? and lx= ?", new Object[]{bdInfo.getZzxbh(),String.valueOf(did),dataType});
                }else{
                 db.execSQL("delete from tbdinfo WHERE  xm= ? and did= ? and lx= ?", new Object[]{bdInfo.getXm(),String.valueOf(did),dataType});
                }
            }
            db.setTransactionSuccessful();  //设置事务成功完成
        } catch (Exception e) {
            log.info("deleteBDInfo:"+e.toString()+"/ls_bh="+ls_bh.toString()+"/did="+did
                    +"/dataType"+dataType);
            L.e("deleteBDInfo", e.toString());
        } finally {
            db.endTransaction();    //结束事务
        }
    }

    public void deleteByzzx(BDInfo bdInfo,Integer did,String dataType){

        db.beginTransaction();  //开始事务
        try {
            db.execSQL("delete from tbdinfo WHERE  zzxbh= ? and did= ? and lx= ?", new Object[]{bdInfo.getZzxbh(),String.valueOf(did),dataType});
            db.setTransactionSuccessful();  //设置事务成功完成
        } catch (Exception e) {
            log.info("deleteByzzx:"+e.toString()+"/bdinfo="+bdInfo.toString()+"/did="+did
                    +"/dataType="+dataType);
            L.e("deleteByzzx", e.toString());
        } finally {
            db.endTransaction();    //结束事务
        }
    }

    public void deleteByxm(BDInfo bdInfo,Integer did,String dataType){

        db.beginTransaction();  //开始事务
        try {
            db.execSQL("delete from tbdinfo WHERE  xm= ? and did= ? and lx= ?", new Object[]{bdInfo.getXm(),String.valueOf(did),dataType});
            db.setTransactionSuccessful();  //设置事务成功完成
        } catch (Exception e) {
            log.info("deleteByxm:"+e.toString()+"/bdInfo="+bdInfo.toString()+"/did="+did
                    +"/dataType="+dataType);
            L.e("deleteByxm", e.toString());
        } finally {
            db.endTransaction();    //结束事务
        }
    }

    public void deleteByzzxbh(String bh,Integer did,String dataType){

        db.beginTransaction();  //开始事务
        try {
            db.execSQL("delete from tbdinfo WHERE  zzxbh= ? and did= ? and lx= ?", new Object[]{bh,String.valueOf(did),dataType});
            db.setTransactionSuccessful();  //设置事务成功完成
        } catch (Exception e) {
            log.info("deleteByzzx:"+e.toString()+"/bh="+bh+"/did="+did
                    +"/dataType="+dataType);
            L.e("deleteByzzx", e.toString());
        } finally {
            db.endTransaction();    //结束事务
        }
    }


    /**
     * 盘点
     *保存待盘点信息
     * */
    public void addPdinfo(List<String> ls_bh,Integer did) {
        db.beginTransaction();  //开始事务
        db.execSQL(" delete from tpdinfo");
        try {
            for(String bh:ls_bh) {
                db.execSQL("INSERT INTO tpdinfo(id,bh,did) VALUES(null, ?, ?)", new Object[]{bh, String.valueOf(did)});
            }
            db.setTransactionSuccessful();  //设置事务成功完成
        } catch (Exception e) {
            log.info("addPdinfo:"+e.toString()+"/ls_bh="+ls_bh.toString()+"/did="+did);
            L.e("addPdinfo", e.toString());
        } finally {
            db.endTransaction();    //结束事务
        }
    }

    /**
    * 查询待盘点信息
     * */
    public List<PDInfo> SelectPDInfo(Integer did){
        Cursor c = null;
        List<PDInfo> ls_pdInfo = new ArrayList<>();
        try {
            c = db.rawQuery(" select * from tpdinfo WHERE did=?", new String[]{String.valueOf(did)});
            while (c.moveToNext()) {
                PDInfo pdInfo = new PDInfo();
                pdInfo.setBh(c.getString(c.getColumnIndex("bh")));
                pdInfo.setZt("0");
                ls_pdInfo.add(pdInfo);
            }
        } catch (Exception e) {
            log.info("SelectPDInfo:"+e.toString()+"/did="+did);
            L.e("SelectBDInfo", e.toString());
        } finally {
            c.close();
        }
        return ls_pdInfo;
    }

    /**
     *删除待盘点信息
     * */
    public void deletePDInfo(Integer did,String bh){

        db.beginTransaction();  //开始事务
        try {
            db.execSQL(" delete from tpdinfo where bh= ? and did= ?",new Object[]{bh,String.valueOf(did)});
            db.setTransactionSuccessful();  //设置事务成功完成
        } catch (Exception e) {
            log.info("deletePDInfo:"+e.toString()+"/did="+did+"/bh="+bh);
            L.e("deletePDInfo", e.toString());
        } finally {
            db.endTransaction();    //结束事务
        }
    }

    /**
     *删除待盘点信息
     * */
    public void deleteAllPDInfo(Integer did){

        db.beginTransaction();  //开始事务
        try {
            db.execSQL(" delete from tpdinfo where did= ?",new Object[]{String.valueOf(did)});
            db.setTransactionSuccessful();  //设置事务成功完成
        } catch (Exception e) {
            log.info("deleteAllPDInfo:"+e.toString()+"/did="+did);
            L.e("deleteAllPDInfo", e.toString());
        } finally {
            db.endTransaction();    //结束事务
        }
    }

    /**
     *删除待盘点信息
     * */
    public void deleteAllMPDInfo(Integer did){

        db.beginTransaction();  //开始事务
        try {
            db.execSQL(" delete from tpdmore where did= ?",new Object[]{String.valueOf(did)});
            db.setTransactionSuccessful();  //设置事务成功完成
        } catch (Exception e) {
            log.info("deleteAllMPDInfo:"+e.toString()+"/did="+did);
            L.e("deleteAllMPDInfo", e.toString());
        } finally {
            db.endTransaction();    //结束事务
        }
    }

    /**
     *判断扫描信息是否在盘点信息中
     * */
    public Boolean IsExistPDbh(String bh,Integer did){
        Boolean isExist = false;
        Cursor c = null;
        try {
            c = db.rawQuery(" select * from tpdinfo WHERE bh=? and did=?", new String[]{bh,String.valueOf(did)});
            if (c.getCount() > 0) {
                isExist = true;
            }
        } catch (Exception e) {
            isExist=false;
            log.info("IsExistPDbh:"+e.toString()+"/bh="+bh+"/did="+did);
            L.e("IsExistPDbh", e.toString());
        } finally {
            c.close();
        }
        return isExist;
    }

    /**
     *保存盘点(扫描)不在计划内信息
     * */
    public void addMPdsminfo(String bh,Integer did) {
        db.beginTransaction();  //开始事务
        try {
            db.execSQL("INSERT INTO tpdmore(id,bh,did) VALUES(null, ?, ?)", new Object[]{bh, String.valueOf(did)});
            db.setTransactionSuccessful();  //设置事务成功完成
        } catch (Exception e) {
            log.info("addMPdsminfo:"+e.toString()+"/bh="+bh+"/did="+did);
            L.e("addMPdsminfo", e.toString());
        } finally {
            db.endTransaction();    //结束事务
        }
    }

    /**
     *保存盘点(扫描)信息
     * */
    public void addPdsminfo(String bh,Integer did) {
        db.beginTransaction();  //开始事务
        try {
            db.execSQL("INSERT INTO tpdzzxinfo(id,bh,did) VALUES(null, ?, ?)", new Object[]{bh, String.valueOf(did)});
            db.setTransactionSuccessful();  //设置事务成功完成
        } catch (Exception e) {
            log.info("addPdsminfo:"+e.toString()+"/"+bh+"/"+did);
            L.e("addPdsminfo", e.toString());
        } finally {
            db.endTransaction();    //结束事务
        }
    }

    /**
     * 查询盘点(扫描)不在计划内信息
     * */
    public List<SCPDInfo> SelectMPDSMInfo(Integer did){
        Cursor c = null;
        List<SCPDInfo> ls_bh = new ArrayList<>();
        try {
            c = db.rawQuery(" select * from tpdmore WHERE did=?", new String[]{String.valueOf(did)});
            while (c.moveToNext()) {
                SCPDInfo scpdInfo = new SCPDInfo();
                scpdInfo.setTagstring(c.getString(c.getColumnIndex("bh")));
                ls_bh.add(scpdInfo);
            }
        } catch (Exception e) {
            log.info("SelectMPDSMInfo:"+e.toString()+"/did="+did);

            L.e("SelectMPDSMInfo", e.toString());
        } finally {
            c.close();
        }
        return ls_bh;
    }

    /**
     * 查询盘点(扫描)信息
     * */
    public List<SCPDInfo> SelectPDSMInfo(Integer did){
        Cursor c = null;
        List<SCPDInfo> ls_bh = new ArrayList<>();
        try {
            c = db.rawQuery(" select * from tpdzzxinfo WHERE did=?", new String[]{String.valueOf(did)});
            while (c.moveToNext()) {
                SCPDInfo scpdInfo = new SCPDInfo();
                scpdInfo.setTagstring(c.getString(c.getColumnIndex("bh")));
                ls_bh.add(scpdInfo);
            }
        } catch (Exception e) {
            log.info("SelectPDSMInfo:"+e.toString()+"/did="+did);
            L.e("SelectPDSMInfo", e.toString());
        } finally {
            c.close();
        }
        return ls_bh;
    }

    /**
     *删除盘点(扫描)信息
     * */
    public void deletePDSMInfo(Integer did,String bh){

        db.beginTransaction();  //开始事务
        try {
            db.execSQL(" delete from tpdzzxinfo where bh= ? and did= ?",new Object[]{bh,String.valueOf(did)});
            db.setTransactionSuccessful();  //设置事务成功完成
        } catch (Exception e) {
            log.info("deletePDInfo:"+e.toString()+"/did="+did+"/bh="+bh);
            L.e("deletePDInfo", e.toString());
        } finally {
            db.endTransaction();    //结束事务
        }
    }

    /**
     *删除盘点(扫描)信息
     * */
    public void deleteAllPDSMInfo(Integer did){

        db.beginTransaction();  //开始事务
        try {

            db.execSQL(" delete from tpdzzxinfo where did= ?",new Object[]{String.valueOf(did)});
            db.setTransactionSuccessful();  //设置事务成功完成
        } catch (Exception e) {
            log.info("deleteAllPDSMInfo:"+e.toString()+"/did="+did);
            L.e("deleteAllPDSMInfo", e.toString());
        } finally {
            db.endTransaction();    //结束事务
        }
    }

    /**
     *删除盘点(扫描)信息
     * */
    public void deleteAllMInfo(Integer did){

        db.beginTransaction();  //开始事务
        try {

            db.execSQL(" delete from tpdmore where did= ?",new Object[]{String.valueOf(did)});
            db.setTransactionSuccessful();  //设置事务成功完成
        } catch (Exception e) {
            log.info("deleteAllMInfo:"+e.toString()+"/did="+did);
            L.e("deleteAllMInfo", e.toString());
        } finally {
            db.endTransaction();    //结束事务
        }
    }

    /**
     *判断扫描信息是否已扫描
     * */
    public Boolean IsExistPDSMbh(String bh,Integer did){
        String sql = "select * from tpdzzxinfo where bh="+bh+"and did="+did;
        Cursor c = null;
        Boolean isExist = false;
        try {
            c = db.rawQuery(" select * from tpdzzxinfo WHERE bh=? and did=?", new String[]{bh,String.valueOf(did)});
            if (c.getCount() > 0) {
                isExist = true;
            }
        } catch (Exception e) {
            isExist=false;
            log.info("IsExistPDSMbh:"+e.toString()+"/bh="+bh+"/did="+did);
            L.e("IsExistPDSMbh", e.toString());
        } finally {
            c.close();
        }
        return isExist;
    }

    /**
     *判断扫描信息是否已扫描
     * */
    public Boolean IsExistMPDSMbh(String bh,Integer did){
        String sql = "select * from tpdmore where bh="+bh+"and did="+did;
        Cursor c = null;
        Boolean isExist = false;
        try {
            c = db.rawQuery(" select * from tpdmore WHERE bh=? and did=?", new String[]{bh,String.valueOf(did)});
            if (c.getCount() > 0) {
                isExist = true;
            }
        } catch (Exception e) {
            isExist=false;
            log.info("IsExistMPDSMbh:"+e.toString()+"/bh="+bh+"/did="+did);
            L.e("IsExistMPDSMbh", e.toString());
        } finally {
            c.close();
        }
        return isExist;
    }



    /**
     * 调拨
     *查询周转箱编号
     * */
    public List<String> selectdbZzxbh(Integer belongid,Integer did,Integer bringid){
        Cursor c = null;
        List<String> ls_bh = new ArrayList<>();
        try {
            c = db.rawQuery(" select * from tzzxdbinfo WHERE belongid=? and did=? and bringid=? ", new String[]{String.valueOf(belongid),String.valueOf(did),String.valueOf(bringid)});
            while (c.moveToNext()) {
                ls_bh.add(c.getString(c.getColumnIndex("bh")));
            }
        } catch (Exception e) {
            log.info("selectdbZzxbh:"+e.toString()+"/belongid="+belongid+"/did="+did+"/bringid="+bringid);
            L.e("selectdbZzxbh", e.toString());
        } finally {
            c.close();
        }
        return ls_bh;
    }

    /**
     * 调拨
     *判断扫描的周转箱编号是否已存在
     * */
    public Boolean IsExistdbZzxbh(String bh,Integer belongid ,Integer did,Integer bringid){
        String sql = "select * from tzzxdbinfo where belongid="+belongid+" and bh="+bh+"and did="+did;
        Cursor c = null;
        Boolean isExist = false;
        try {
            c = db.rawQuery(" select * from tzzxdbinfo WHERE belongid=? and bh=? and did=? and bringid=?", new String[]{String.valueOf(belongid),bh,String.valueOf(did),String.valueOf(bringid)});

            if (c.getCount() > 0) {
                isExist = true;
            }
        } catch (Exception e) {
            isExist=false;
            log.info("IsExistdbZzxbh:"+e.toString()+"/bh="+bh+"/belongid="+belongid+"/did="+did);
            L.e("IsExistdbZzxbh", e.toString());
        } finally {
            c.close();
        }
        return isExist;
    }

    /**
     * 调拨
     *保存分配的周转箱编号
     * */
    public void addDbZzxbh(String bh,Integer belongid ,Integer did,Integer bringid) {
        db.beginTransaction();  //开始事务
        try {
            db.execSQL("INSERT INTO tzzxdbinfo(id,bh,belongid,did,bringid) VALUES(null, " +
                    "?, ?, ?, ?)", new Object[]{bh,String.valueOf(belongid),String.valueOf(did),String.valueOf(bringid)});
            db.setTransactionSuccessful();  //设置事务成功完成
        } catch (Exception e) {
            log.info("addDbZzxbh:"+e.toString()+"/bh="+bh+"/belongid="+belongid+"/did="+did+"/bringid="+bringid);
            L.e("addDbZzxbh", e.toString());
        } finally {
            db.endTransaction();    //结束事务
        }
    }


    /**
     * 调拨
     *删除周转箱编号
     * */
    public void deletedbZzxbh(List<String> ls_bh,Integer belongid,Integer did,Integer bringid){
        db.beginTransaction();  //开始事务
        try {
            for(String id:ls_bh) {
                db.execSQL("delete from tzzxdbinfo WHERE belongid= ? and bh= ? and did= ? and bringid= ?", new Object[]{String.valueOf(belongid),id,String.valueOf(did),String.valueOf(bringid)});
            }
            db.setTransactionSuccessful();  //设置事务成功完成
        } catch (Exception e) {
            log.info("deletedbZzxbh:"+e.toString()+"/ls_bh="+ls_bh.toString()+"/belongid="
                    +belongid+"/did="+did+"/bringid="+bringid);
            L.e("deletedbZzxbh", e.toString());
        } finally {
            db.endTransaction();    //结束事务
        }
    }



}
